#! /usr/bin/python3 python3
# -*- coding: utf-8 -*-

# *************************************************************
#  Projectname @  pysm
#     Filename @  CommonModel.py
#       Author @  Steve.ma
#  Create date @  2018/8/31
#  Description @  
# *************************************************************

from Library.Db import*

SMSql = SMSql()
class CommonModel() :
    table = ''
    lastsql = ''
    def __init__(self):
        return
    #设置表
    def setTable(self, table):
        self.table = table
        return
    #公共 详情
    def Info(self, fields = '*', condition = '', order = '', group = '', joins=''):
        return self.s_getRow(self.table, fields, condition, order, group, joins)
    #公共 列表
    def List(self, fields = '*', condition = '', order = '', limit = '', group = '', joins=''):
        return self.s_getAll(self.table, fields, condition, order, limit, group, join)
    #公共 插入
    def Ins(self, condition = ''):
        return self.s_insert(self.table, condition)
    #公共 修改
    def Upd(self, data = '', condition = ''):
        return self.s_update(self.table, data, condition)

    #s_获取单条数据
    def s_getRow(self, table, fields = '*', condition = '', order = '', group = '', joins=''):
        #写入fields和table
        sql = 'SELECT '+fields+' FROM `'+table+'` '
        args = []

        #是否联查
        if isinstance(joins, dict) and joins:
            t = 'a.'
            sql = sql + 'AS a LEFT JOIN ' + joins['table'] + ' AS '+ joins['as'] + ' ON ' + joins['on'] + ' WHERE '
        else :
            t = ''
            sql = sql + 'WHERE '

        #条件的写入
        keys = []
        if isinstance(condition,dict) and condition:
            #循环条件
            for k, v in condition.items():
                if isinstance(v, dict) and v['opera'] :
                    #联查
                    if 't' in v :
                        t = v['t']
                    #条件
                    if v['opera'] == 'LIKE':
                        keys.append(t + '`' + k + '`' + ' LIKE \%' + '%s' + '\% ')
                    elif v['opera'] == 'IN':
                        keys.append(t + '`' + k + '`' + ' IN(' + '%s' + ') ')
                    elif v['opera'] == 'NOT IN':
                        keys.append(t + '`' + k + '`' + ' NOT IN(' + '%s' + ') ')
                    elif v['opera'] == 'GT':
                        keys.append(t + '`' + k + '`' + ' > ' + '%s' + ' ')
                    elif v['opera'] == 'LT':
                        keys.append(t + '`' + k + '`' + ' < ' + '%s' + ' ')
                    elif v['opera'] == 'NEQ':
                        keys.append(t + '`' + k + '`' + ' != ' + '%s' + ' ')
                    elif v['opera'] == 'BETWEEN':
                        keys.append(t + '`' + k + '`' + ' BETWEEN ' + '%s' + ' AND ' + '%s ')

                    # args.append(str(k))
                    args.append(str(v['value']))
                    if v['opera'] == 'BETWEEN':
                        args.append(str(v['value2']))


                    if v['opera'] == 'OR':
                        orsql = ''
                        if v['value3']:
                            orsql = orsql + 'OR ' + v['value3']
                        if v['value4']:
                            orsql = orsql + 'OR ' + v['value4']
                        keys.append('( '+ v['value'] + ' OR ' + v['value2'] + orsql + ' ) ')

                else :
                    keys.append(t + '`' + k + '`' + ' = ' + '%s' + ' ')
                    # args.append(str(k))
                    args.append(str(v))
            #keys 要转成字符串拼接到sql中
            sql = sql + 'AND '.join(keys)
        else :
            #条件为空
            sql = sql + ' 1=1 '

        #GROUP  GROUP 是字符串
        groupkey = []
        if isinstance(group, str) and group:
            sql = sql + ' GROUP BY ' + group

        #ORDER
        orderkey = []
        if isinstance(order, dict) and order:
            for ork, orv in order.items():
                orderkey.append('`'+ork+'` '+ orv);
            sql = sql + ' ORDER BY ' + ",".join(orderkey)

        sql = sql + ' LIMIT 1'
        self.lastsql = sql % tuple(args)
        return SMSql.queryOne(sql, tuple(args))
    #s_获取多条数据
    def s_getAll(self, table, fields = '*', condition = '', order = '', limit = '', group = '', joins=''):
        #写入fields和table
        sql = 'SELECT '+fields+' FROM `'+table+'` '
        args = []

        #是否联查
        if isinstance(joins, dict) and joins:
            t = 'a.'
            sql = sql + 'AS a LEFT JOIN ' + joins['table'] + ' AS '+ joins['as'] + ' ON ' + joins['on'] + ' WHERE '
        else :
            t = ''
            sql = sql + 'WHERE '

        #条件的写入
        keys = []
        if isinstance(condition,dict) and condition:
            #循环条件
            for k, v in condition.items():
                if isinstance(v, dict) and v['opera'] :
                    #联查
                    if 't' in v :
                        t = v['t']
                    #条件
                    if v['opera'] == 'LIKE':
                        keys.append(t + '`' + k + '`' + ' LIKE \%' + '%s' + '\% ')
                    elif v['opera'] == 'IN':
                        keys.append(t + '`' + k + '`' + ' IN(' + '%s' + ') ')
                    elif v['opera'] == 'NOT IN':
                        keys.append(t + '`' + k + '`' + ' NOT IN(' + '%s' + ') ')
                    elif v['opera'] == 'GT':
                        keys.append(t + '`' + k + '`' + ' > ' + '%s' + ' ')
                    elif v['opera'] == 'LT':
                        keys.append(t + '`' + k + '`' + ' < ' + '%s' + ' ')
                    elif v['opera'] == 'NEQ':
                        keys.append(t + '`' + k + '`' + ' != ' + '%s' + ' ')
                    elif v['opera'] == 'BETWEEN':
                        keys.append(t + '`' + k + '`' + ' BETWEEN ' + '%s' + ' AND ' + '%s ')

                    # args.append(str(k))
                    args.append(str(v['value']))
                    if v['opera'] == 'BETWEEN':
                        args.append(str(v['value2']))


                    if v['opera'] == 'OR':
                        orsql = ''
                        if v['value3']:
                            orsql = orsql + 'OR ' + v['value3']
                        if v['value4']:
                            orsql = orsql + 'OR ' + v['value4']
                        keys.append('( '+ v['value'] + ' OR ' + v['value2'] + orsql + ' ) ')

                else :
                    keys.append(t + '`' + k + '`' + ' = ' + '%s' + ' ')
                    # args.append(str(k))
                    args.append(str(v))
            #keys 要转成字符串拼接到sql中
            sql = sql + 'AND '.join(keys)
        else :
            #条件为空
            sql = sql + ' 1=1 '

        #GROUP  GROUP 是字符串
        groupkey = []
        if isinstance(group, str) and group:
            sql = sql + ' GROUP BY ' + group

        #ORDER
        orderkey = []
        if isinstance(order, dict) and order:
            for ork, orv in order.items():
                orderkey.append('`'+ork+'` '+ orv);
            sql = sql + ' ORDER BY ' + ",".join(orderkey)

        #LIMIT
        if isinstance(limit, list) and limit:
            sql = sql + ' LIMIT ' + str(limit[0]) + ',' + str(limit[1])
        self.lastsql = sql % tuple(args)
        return SMSql.queryAll(sql, tuple(args))
    #s_更新数据信息
    def s_update(self, table, data = '', condition = ''):
        sql = 'UPDATE `'+table+'` SET '
        args = []
        # 写入的值
        keys = []
        if isinstance(data,dict) and data:
            #循环条件
            for k, v in data.items():
                keys.append('`' + k + '` = ' + '%s')
                args.append(str(v))
            #keys 要转成字符串拼接到sql中
            sql = sql + ', '.join(keys)
        else :
            #条件为空
            return False

        sql = sql + ' WHERE '
        # 条件的写入
        keys = []
        if isinstance(condition, dict) and condition:
            # 循环条件
            for k, v in condition.items():
                if isinstance(v, dict) and v['opera']:
                    # 条件
                    if v['opera'] == 'LIKE':
                        keys.append('`' + k + '`' + ' LIKE \%' + '%s' + '\% ')
                    elif v['opera'] == 'IN':
                        keys.append('`' + k + '`' + ' IN(' + '%s' + ') ')
                    elif v['opera'] == 'NOT IN':
                        keys.append('`' + k + '`' + ' NOT IN(' + '%s' + ') ')
                    elif v['opera'] == 'GT':
                        keys.append('`' + k + '`' + ' > ' + '%s' + ' ')
                    elif v['opera'] == 'LT':
                        keys.append('`' + k + '`' + ' < ' + '%s' + ' ')
                    elif v['opera'] == 'NEQ':
                        keys.append('`' + k + '`' + ' != ' + '%s' + ' ')
                    elif v['opera'] == 'BETWEEN':
                        keys.append('`' + k + '`' + ' BETWEEN ' + '%s' + ' AND ' + '%s ')

                    # args.append(str(k))
                    args.append(str(v['value']))
                    if v['opera'] == 'BETWEEN':
                        args.append(str(v['value2']))

                    if v['opera'] == 'OR':
                        orsql = ''
                        if v['value3']:
                            orsql = orsql + 'OR ' + v['value3']
                        if v['value4']:
                            orsql = orsql + 'OR ' + v['value4']
                        keys.append('( ' + v['value'] + ' OR ' + v['value2'] + orsql + ' ) ')
                else:
                    keys.append('`' + k + '`' + ' = ' + '%s' + ' ')
                    # args.append(str(k))
                    args.append(str(v))
            # keys 要转成字符串拼接到sql中
            sql = sql + 'AND '.join(keys)
        else:
            # 条件为空
            return False
        self.lastsql = sql % tuple(args)
        res = SMSql.query(sql, tuple(args))
        if SMSql.transaction == False :
            SMSql.commit()
        return res
    #s_插入数据
    def s_insert(self, table, condition = ''):
        #写入fields和table
        sql = 'INSERT INTO `'+table+'` ('
        args = []
        keys = []
        vals = []
        if isinstance(condition,dict) and condition:
            #循环条件
            for k, v in condition.items():
                keys.append('`' + k + '`')
                vals.append('%s')
                args.append(str(v))
            #keys 要转成字符串拼接到sql中
            sql = sql + ','.join(keys) + ') VALUES (' + ','.join(vals) + ')'
        else :
            #条件为空
            return False
        self.lastsql = sql % tuple(args)
        res =  SMSql.queryAdd(sql, tuple(args))
        if SMSql.transaction == False :
            SMSql.commit()
        return res
    def begin(self):
        SMSql.begin()
        return
    def commit(self):
        SMSql.commit()
        return
    def rollback(self):
        SMSql.rollback()
        return
